package edu.iiitb.gis.vts.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;



import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import edu.iiitb.gis.vts.model.VehiclePosition;
import edu.iiitb.gis.vts.util.DBconn;

import com.opensymphony.xwork2.*;

public class VehicleService {
	private DBconn dbconn = null;
	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet resultSet1 = null;
	private String query1 = null;
	
	public VehiclePosition getVehiclePosition(String vehicleID) {
		VehiclePosition vehicleposition = new VehiclePosition();
		
		try {
			query1 = "SELECT latitude, longitude, time FROM GPS_DATA where vehicleID=? order by time desc limit 1";
			dbconn = new DBconn();
			con = (Connection) dbconn.getConnection();
			pst = (PreparedStatement) con.prepareStatement(query1);
			pst.setString(1,vehicleID);
			

			resultSet1 = pst.executeQuery();
			
			while(resultSet1.next()) {
				vehicleposition.setVehicleID(vehicleID);
				vehicleposition.setLatitude(resultSet1.getString("latitude"));
				vehicleposition.setLongitude(resultSet1.getString("longitude"));
				vehicleposition.setTime(resultSet1.getString("time"));
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			dbconn.close(resultSet1);
			dbconn.close(pst);
			dbconn.close(con);
			
		}
		return vehicleposition;
	}
	
	
	public ArrayList<String> getvehicleIDs() {
		ArrayList<String> vehicleIDList = new ArrayList<String>();
		
		try {
			query1 = "SELECT vehicleNo FROM VEHICLES";
			dbconn = new DBconn();
			con = (Connection) dbconn.getConnection();
			pst = (PreparedStatement) con.prepareStatement(query1);
			
			resultSet1 = pst.executeQuery();
			
			while(resultSet1.next()) {
				vehicleIDList.add(resultSet1.getString("vehicleNo"));
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			dbconn.close(resultSet1);
			dbconn.close(pst);
			dbconn.close(con);
			
		}
		return vehicleIDList;
	}
	
	public ArrayList<String> getvehicleIDsForGroup(String groupID) {
		ArrayList<String> vehicleIDList = new ArrayList<String>();
		
		try {
			query1 = "SELECT vehicleNo FROM vehicles WHERE groupID=?";
			dbconn = new DBconn();
			con = (Connection) dbconn.getConnection();
			pst = (PreparedStatement) con.prepareStatement(query1);
			pst.setString(1,groupID);
			
			resultSet1 = pst.executeQuery();
			
			while(resultSet1.next()) {
				vehicleIDList.add(resultSet1.getString("vehicleNo"));
				System.out.println(resultSet1.getString("vehicleNo"));
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			dbconn.close(resultSet1);
			dbconn.close(pst);
			dbconn.close(con);
			
		}
		return vehicleIDList;
	}
	
}
